package com.frame.project.repository.system.menu;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.frame.framework.web.repository.BaseRepository;
import com.frame.project.domain.system.menu.Menu;

/**
 * 
 * @className ：MenuRepository
 * @describe ：菜单
 */
@Repository
public interface MenuRepository extends BaseRepository<Menu, Long>{

	@Query(nativeQuery = true, value = "select distinct m.id, m.parent_id as parentId, m.menu_name as menuName, m.url, m.visible, ifnull(m.perms,'') as perms, m.target, m.menu_type as menuType, m.icon, m.order_num as orderNum" 
			+ " from sys_menu m where m.menu_type in ('M', 'C') and m.visible = '0' order by m.parent_id, m.order_num")
	List<Map<String, Object>> selectMenuNormalAll();
	
	@Query(nativeQuery = true, value = "select distinct m.id, m.parent_id as parentId, m.menu_name as menuName, m.url, m.visible, ifnull(m.perms,'') as perms, m.target, m.menu_type as menuType, m.icon, m.order_num as orderNum"
			+ " from sys_menu m left join sys_role_menu rm on m.id = rm.menu_id"
			+ " left join sys_user_role ur on rm.role_id = ur.role_id"
			+ " left join sys_role ro on ur.role_id = ro.id"
			+ " where ur.user_id = ?1 and m.menu_type in ('M', 'C') and m.visible = '0'  AND ro.status = '0'"
			+ " order by m.parent_id, m.order_num")
	List<Map<String, Object>> selectMenusByUserId(Long userId);
	
	@Query(nativeQuery = true, value = "select distinct m.id, m.parent_id as parentId, m.menu_name as menuName, m.url, m.visible, m.is_refresh as isRefresh, ifnull(m.perms,'') as perms, m.target, m.menu_type as menuType, m.icon, m.order_num as orderNum"
			+ " from sys_menu m left join sys_role_menu rm on m.id = rm.menu_id"
			+ " left join sys_user_role ur on rm.role_id = ur.role_id"
			+ " left join sys_role ro on ur.role_id = ro.id"
			+ " where ur.user_id = ?1 and (m.menu_name like concat('%', ?2, '%') or ?2 is null)"
			+ " and (m.visible = ?3 or ?3 is null)"
			+ " order by m.parent_id, m.order_num")
	List<Map<String, Object>> selectMenuListByUserId(Long userId, String menuName, String visible);
	
	@Query(nativeQuery = true, value = "select distinct m.id, m.parent_id as parentId, m.menu_name as menuName, m.url, m.visible, ifnull(m.perms,'') as perms, m.target, m.menu_type as menuType, m.icon, m.order_num as orderNum"
			+ " from sys_menu m left join sys_role_menu rm on m.id = rm.menu_id"
			+ " left join sys_user_role ur on rm.role_id = ur.role_id"
			+ " left join sys_role ro on ur.role_id = ro.id"
			+ " where ur.user_id = ?1 order by m.parent_id, m.order_num")
	List<Map<String, Object>> selectMenuAllByUserId(Long userId);
	
	@Query(nativeQuery = true, value = "select distinct m.perms from sys_menu m"
			+ " left join sys_role_menu rm on m.id = rm.menu_id"
			+ " left join sys_user_role ur on rm.role_id = ur.role_id"
			+ " left join sys_role r on r.id = ur.role_id"
			+ " where m.visible = '0' and r.status = '0' and ur.user_id = ?1")
	List<String> selectPermsByUserId(Long userId);
	
	@Query(nativeQuery = true, value = "select concat(m.id, ifnull(m.perms,'')) as perms from sys_menu m"
			+ " left join sys_role_menu rm on m.id = rm.menu_id"
			+ " where rm.role_id = ?1 order by m.parent_id, m.order_num")
	List<String> selectMenuTree(Long roleId);
	
	@Query(nativeQuery = true, value = "select count(1) from sys_menu where parent_id=?1")
	Integer selectCountMenuByParentId(Long parentId);
	
	Menu findByMenuNameAndParentId(String menuName, Long parentId);
	
	
}
